مقایسه کارایی Join جدول ها برای انواع داده ای INT و BIGINT و GUID

--/// Parent Table
CREATE TABLE parent_int

                            ([pk_col] [INT] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE parent_bigint

                            ([pk_col] [bigint] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE parent_guid

                            ([pk_col] [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));


--/// Detail Table
CREATE TABLE detail_int

                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [INT], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE detail_bigint

                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [bigint], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE detail_guid

                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [UNIQUEIDENTIFIER], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));


--/// Add Relation
ALTER TABLE dbo.detail_int
ADD CONSTRAINT FK_detail_int_parent_int
FOREIGN KEY (join_col) REFERENCES dbo.parent_int (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;

ALTER TABLE dbo.detail_bigint
ADD CONSTRAINT FK_detail_bigint_parent_int
FOREIGN KEY (join_col) REFERENCES dbo.parent_bigint (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;

ALTER TABLE dbo.detail_guid 
ADD CONSTRAINT FK_detail_guid_parent_guid
FOREIGN KEY (join_col) REFERENCES dbo.parent_guid (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;


--/// Add Index
CREATE INDEX detail_int_join_col ON dbo.detail_int (join_col);
CREATE INDEX detail_bigint_join_col ON dbo.detail_bigint (join_col);
CREATE INDEX detail_guid_join_col ON dbo.detail_guid (join_col);


--/// Data Load
DECLARE @returnid table (pk_col uniqueidentifier)
DECLARE @val int
DECLARE @RANDval int
DECLARE @val2 int
DECLARE @pkint int
DECLARE @pkguid uniqueidentifier
DECLARE @pkseqguid uniqueidentifier
SELECT @val=1

WHILE @val < 200000 BEGIN 
   INSERT INTO parent_int (pk_col, col1, col2, col3) 
      VALUES (@val, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   INSERT INTO parent_bigint (pk_col, col1, col2, col3) 
      VALUES (@val, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   SELECT @pkint = @val;
   SELECT @pkguid = newid();

   INSERT INTO parent_guid (pk_col, col1, col2, col3) 
     VALUES (@pkguid, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   SELECT @pkseqguid = r.pk_col
FROM @returnid r

   SELECT @RANDval=FLOOR(RAND()*(10-1)+1);
   SELECT @val2=1

   WHILE @val2 <= @RANDval BEGIN
      INSERT INTO detail_int (join_col, col1, col2, col3)
         VALUES (@pkint, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      INSERT INTO detail_bigint (join_col, col1, col2, col3)
         VALUES (@pkint, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      INSERT INTO detail_guid (join_col, col1, col2, col3)
         VALUES (@pkguid, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      SELECT @val2=@val2+1
   END
   
   SELECT @val=@val+1
END

--/// Fetch All Records in Parent
SELECT *
FROM parent_int p
INNER JOIN detail_int d ON d.join_col=p.pk_col;

SELECT *
FROM parent_bigint p
INNER JOIN detail_bigint d ON d.join_col=p.pk_col;

SELECT *
FROM parent_guid p
INNER JOIN detail_guid d ON d.join_col=p.pk_col;

 
--// Fetch Single Record in Parent
SELECT *
FROM parent_int p
INNER JOIN detail_int d ON d.join_col=p.pk_col 
WHERE p.pk_col=121143;

SELECT *
FROM parent_bigint p 
INNER JOIN detail_bigint d ON d.join_col=p.pk_col 
WHERE p.pk_col=121143;

SELECT *
FROM parent_guid p 
INNER JOIN detail_guid d ON d.join_col=p.pk_col 
WHERE p.pk_col='A10B3C3C-3ABC-47CB-8DDE-22DC1DF89447';